import pandas as pd
from sqlalchemy import create_engine, Column, Integer, DateTime, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sklearn.linear_model import LinearRegression
import matplotlib.pyplot as plt
import datetime

# ======================================
# 1. 数据库配置与实体类定义
# ======================================
# 数据库连接配置（替换为你的实际配置）
DATABASE_URI = "mysql+pymysql://root:123456@localhost:3306/blood_sugar"
engine = create_engine(DATABASE_URI)
Session = sessionmaker(bind=engine)

# 定义实体类（映射数据库表）
Base = declarative_base()


class BloodSugarData(Base):
    __tablename__ = "blood_sugar"  # 对应数据库表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    time = Column(DateTime)
    amount = Column(Float)
    calorie = Column(Float)


# ======================================
# 2. 数据库访问层
# ======================================
def get_db_session():
    """获取数据库会话"""
    return Session()


def query_all_data(session):
    """查询所有血糖数据"""
    return session.query(BloodSugarData).all()


def query_data_by_time_range(session, start_time, end_time):
    """按时间范围查询数据"""
    return session.query(BloodSugarData).filter(
        BloodSugarData.time >= start_time,
        BloodSugarData.time <= end_time
    ).all()


# ======================================
# 3. 数据处理与预测模型
# ======================================
def prepare_prediction_data(data_list):
    """准备预测模型的输入数据"""
    if not data_list:
        return None, None

    # 提取特征和目标值（假设用 amount 预测 calorie）
    X = [[item.amount] for item in data_list]
    y = [item.calorie for item in data_list]
    return X, y


def train_simple_model(X, y):
    """训练简单的线性回归模型"""
    if not X or not y:
        print("数据不足，无法训练模型")
        return None

    model = LinearRegression()
    model.fit(X, y)
    return model


def predict_with_model(model, amount_value):
    """用模型进行预测"""
    if not model:
        return None
    return model.predict([[amount_value]])[0]


# ======================================
# 4. 数据可视化
# ======================================
def visualize_data(data_list):
    """可视化血糖数据"""
    if not data_list:
        print("没有数据可可视化")
        return

    times = [item.time for item in data_list]
    amounts = [item.amount for item in data_list]
    calories = [item.calorie for item in data_list]

    plt.figure(figsize=(12, 6))

    plt.subplot(2, 1, 1)
    plt.plot(times, amounts, 'b-', label='Amount')
    plt.title('Blood Sugar Amount Over Time')
    plt.xlabel('Time')
    plt.ylabel('Amount')
    plt.legend()
    plt.grid(True)

    plt.subplot(2, 1, 2)
    plt.plot(times, calories, 'r-', label='Calorie')
    plt.title('Calorie Over Time')
    plt.xlabel('Time')
    plt.ylabel('Calorie')
    plt.legend()
    plt.grid(True)

    plt.tight_layout()
    plt.show()


# ======================================
# 5. 主函数
# ======================================
def main():
    print("===== 血糖预测系统启动 =====")

    # 1. 连接数据库并查询数据
    session = get_db_session()
    try:
        print("正在查询数据库数据...")
        all_data = query_all_data(session)
        print(f"查询到 {len(all_data)} 条血糖数据")

        if not all_data:
            print("数据库中没有数据，请先导入数据")
            return

        # 2. 可视化原始数据
        print("正在可视化数据...")
        visualize_data(all_data)

        # 3. 准备预测数据
        print("正在准备模型训练数据...")
        X, y = prepare_prediction_data(all_data)

        # 4. 训练简单预测模型
        print("正在训练预测模型...")
        model = train_simple_model(X, y)
        if not model:
            return

        # 5. 示例：用模型进行预测
        test_amount = 50.0  # 假设输入量为 50
        prediction = predict_with_model(model, test_amount)
        print(f"预测输入量为 {test_amount} 时的卡路里值: {prediction:.2f}")

        # 6. 示例：按时间范围查询数据
        one_week_ago = datetime.datetime.now() - datetime.timedelta(days=7)
        recent_data = query_data_by_time_range(session, one_week_ago, datetime.datetime.now())
        print(f"最近7天查询到 {len(recent_data)} 条数据")

    except Exception as e:
        print(f"执行过程中出错: {e}")
    finally:
        # 关闭数据库会话
        session.close()
        print("===== 程序执行完毕 =====")


if __name__ == '__main__':
    main()